# ~*~ coding: utf-8 ~*~
import json
import traceback

from apps.extensions import db
from apps.common.utils import date_obj_to_str, date_str_to_obj, datetime_obj_to_str, datetime_str_to_obj
from flask import current_app

OPERATOR_FUNC_DICT = {
    '=': (lambda cls, k, v: getattr(cls, k) == v),
    '==': (lambda cls, k, v: getattr(cls, k) == v),
    'eq': (lambda cls, k, v: getattr(cls, k) == v),
    '!=': (lambda cls, k, v: getattr(cls, k) != v),
    'ne': (lambda cls, k, v: getattr(cls, k) != v),
    'neq': (lambda cls, k, v: getattr(cls, k) != v),
    '>': (lambda cls, k, v: getattr(cls, k) > v),
    'gt': (lambda cls, k, v: getattr(cls, k) > v),
    '>=': (lambda cls, k, v: getattr(cls, k) >= v),
    'gte': (lambda cls, k, v: getattr(cls, k) >= v),
    '<': (lambda cls, k, v: getattr(cls, k) < v),
    'lt': (lambda cls, k, v: getattr(cls, k) < v),
    '<=': (lambda cls, k, v: getattr(cls, k) <= v),
    'lte': (lambda cls, k, v: getattr(cls, k) <= v),
    # 'or': (lambda cls, k, v: or_(getattr(cls, k) == value for value in v)),
    # 'and': (lambda cls, k, v: and_(getattr(cls, k) == value for value in v)),
    'in': (lambda cls, k, v: getattr(cls, k).in_(v)),
    'nin': (lambda cls, k, v: ~getattr(cls, k).in_(v)),
    'like': (lambda cls, k, v: getattr(cls, k).like('%%%s%%' % v)),
    'nlike': (lambda cls, k, v: ~getattr(cls, k).like(v)),
    '+': (lambda cls, k, v: getattr(cls, k) + v),
    'incr': (lambda cls, k, v: getattr(cls, k) + v),
    '-': (lambda cls, k, v: getattr(cls, k) - v),
    'decr': (lambda cls, k, v: getattr(cls, k) - v),
}


def generate_binary_expression(cls, filter_conditions):

    def _change_type(cls, field, value):
        """ 有些表字段比如DateTime类型比较的时候需要转换类型，
        前端传过来的都是字符串，Date等类型没法直接相比较，需要转成Date类型
        Args:
            cls (class): Model class
            field (str): Model class field
            value (str): value need to compare
        """
        field_type = getattr(cls, field).type
        if isinstance(field_type, db.Date):
            return date_str_to_obj(value)
        elif isinstance(field_type, db.DateTime):
            return datetime_str_to_obj(value)
        else:
            return value

    binary_expression_list = []
    for item in filter_conditions:
        if not item:  # 允许无效的空条件， 例如：[(), ('name', 'eq', 'zhangsan')]
            continue
        field = item[0]
        op = item[1]
        val = item[2]
        if val == '':   # 允许值为空，为获取全部数据， 例如：('name', 'like', '')
            continue
        if val is not None:
            op_val = _change_type(cls, field, val)
            if op in OPERATOR_FUNC_DICT:
                binary_expression_list.append(
                    OPERATOR_FUNC_DICT[op](cls, field, op_val)
                )
    return binary_expression_list


def technology_expression(cls, filter_conditions):
    """
    计算生成的二进制表达式
    :param cls: Model
    :param filter_conditions:
        {
            'and':{
                'and': [('create_time', '<', 'end_time'),('create_time', '>', 'end_time')],
                'or': [('name', 'eq', 'wang'), ('telephone', '=', 12)],
            }
        }
    :return:
    """
    finally_binary_expression = []
    for key, val in filter_conditions.items():
        for k, v in val.items():
            if k == 'and' and v:
                binary_expression_list = generate_binary_expression(cls, v)
                finally_binary_expression.append(db.and_(*binary_expression_list))
            if k == 'or' and v:
                binary_expression_list = generate_binary_expression(cls, v)
                finally_binary_expression.append(db.or_(*binary_expression_list))

        if key == 'and':
            return db.and_(*finally_binary_expression)
        if key == 'or':
            return db.or_(*finally_binary_expression)


def parse_operator(cls, filter_conditions):
    """ 用来返回sqlalchemy query对象filter使用的表达式
    Args:
        cls: Model
        filter_conditions (列表): 过滤条件列表形式
        [
            ('last_name', 'eq', 'wang'),
            ('age', '>', 12),
            ('create_time', '<', 'end_time'),
            ('create_time', '>', 'end_time'),
        ]
        filter_conditions (dict): 过滤条件字典形式
        {
            'and':{
                'and': [('create_time', '<', 'end_time'),('create_time', '>', 'end_time')],
                'or': [('name', 'eq', 'wang'), ('telephone', '=', 12)],
            }
        }

        {
            'and': [('create_time', '<', 'end_time'),('create_time', '>', 'end_time')],
            'or': [('name', 'eq', 'wang'), ('telephone', '=', 12)],
        }
    Returns:
        binary_expression
    """

    if isinstance(filter_conditions, list):  # [('create_time', '<', 'end_time'),('create_time', '>', 'end_time')]
        filter_conditions = {'and': filter_conditions}

    if isinstance(filter_conditions, dict):
        for value in filter_conditions.values():
            if isinstance(value, list):  # {'and': [], 'or':[]}
                filter_conditions = {'and': filter_conditions}
                break

            if isinstance(value, dict):  # {'and': {'and': [], 'or':[]}}
                filter_conditions = filter_conditions
                break
    return technology_expression(cls, filter_conditions)


class CRUDMixin(object):
    """Mixin that adds convenience methods for
    CRUD (create, read, update, delete) operations."""

    @classmethod
    def create(cls, commit=True, **kwargs):
        """Create a new record and save it the database."""
        instance = cls(**kwargs)
        return instance.save(commit)

    @classmethod
    def create_from_dict(cls, d, commit=True):
        """Create a new record and save it the database."""
        assert isinstance(d, dict)
        instance = cls(**d)
        return instance.save(commit)

    def update(self, commit=True, **kwargs):
        """Update specific fields of a record."""
        for attr, value in list(kwargs.items()):
            setattr(self, attr, value)
        return commit and self.save(commit) or self

    def save(self, commit=True):
        """Save the record."""
        db.session.add(self)
        if commit:
            try:
                db.session.commit()
            except Exception as e:
                current_app.logger.error(traceback.format_exc())
                db.session.rollback()
        return self

    def delete(self, commit=True):
        """Remove the record from the database."""
        db.session.delete(self)
        if commit:
            try:
                db.session.commit()
            except Exception as e:
                current_app.logger.error(traceback.format_exc())
                db.session.rollback()
        return self

    @classmethod
    def create_or_update(cls, query_dict, update_dict=None):
        instance = db.session.query(cls).filter_by(**query_dict).first()
        if instance:  # update
            if update_dict is not None:
                return instance.update(**update_dict)
            else:
                return instance
        else:  # create new instance
            query_dict.update(update_dict or {})
            return cls.create(**query_dict)

    @classmethod
    def get_queryset(cls, fields=None, queryset=None, filter_conditions=None, order_by_list=None, offset=0, limit=12):
        """
        获取查询结果集
        :param fields:  需要查询的字段
        :param queryset: BaseQuery对象
        :param filter_conditions:  需要过滤的字段
        :param order_by_list:  需要排序的字段
        :param offset:  偏移量
        :param limit:   每页显示的多少条
        :return:  query对象
        """
        fields = (
            [getattr(cls, column) for column in fields] if fields is not None
            else None
        )
        if fields:
            query = db.session.query(*fields)
        else:
            query = db.session.query(cls)

        if queryset:
            query = queryset

        if filter_conditions:
            filter_binary_expression = parse_operator(cls, filter_conditions)
            query = query.filter(filter_binary_expression)

        if order_by_list:
            for (field, order) in order_by_list:
                query = (
                    query.order_by(db.asc(getattr(cls, field))) if order == 1 or order == 'asc' else
                    query.order_by(db.desc(getattr(cls, field)))
                )

        if limit:
            results = query.offset(offset).limit(limit).all()
        else:
            results = query.all()

        count = query.count()
        return results, count


class BaseModel(CRUDMixin, db.Model):
    __abstract__ = True

    __table_args__ = {
        'mysql_engine': 'InnoDB',  # 创建表时设置存储引擎
        'mysql_charset': 'utf8'    # 创建表时设置存储字符集
    }

    def _gen_tuple(self):
        for col in self.__table__.columns:
            if isinstance(col.type, db.DateTime):
                value = datetime_obj_to_str(getattr(self, col.name))
            elif isinstance(col.type, db.Date):
                value = date_obj_to_str(getattr(self, col.name))
            elif isinstance(col.type, db.Numeric):
                value = float(getattr(self, col.name))
            else:
                value = getattr(self, col.name)
            yield (col.name, value)

    def to_dict(self):
        return dict(self._gen_tuple())

    def to_json(self):
        return json.dumps(self.to_dict())
